package com.mayn.action;

import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.AnsiSqlDialect;
import com.jfinal.plugin.c3p0.C3p0Plugin;
import com.mayn.common.TaobaoApiConfig;
import com.mayn.util.DateUtil;
import com.taobao.api.ApiException;
import com.taobao.api.DefaultTaobaoClient;
import com.taobao.api.TaobaoClient;
import com.taobao.api.domain.CainiaoBillQueryOrderinfolist;
import com.taobao.api.request.WlbWmsCainiaoBillQueryRequest;
import com.taobao.api.request.WlbWmsConsignBillGetRequest;
import com.taobao.api.request.WlbWmsReturnBillGetRequest;
import com.taobao.api.response.WlbWmsCainiaoBillQueryResponse;
import com.taobao.api.response.WlbWmsConsignBillGetResponse;
import com.taobao.api.response.WlbWmsReturnBillGetResponse;


public class CainiaoAction {
	private static final Logger LOG = LoggerFactory.getLogger(CainiaoAction.class);
	static String url = TaobaoApiConfig.JstUrl;
	
	/*
	 * 店铺枚举类
	 */
	public static interface ShopIdEnum {
		int nmsm = 5; 
		int mltsm = 10;
	}
	public static interface ShopNameEnum {
		String nmsm = "宁美国度官方旗舰店";
		String mltsm = "名龙堂官方旗舰店";
	}
	public static String getShopName(int shopId){
		if(shopId == ShopIdEnum.nmsm){
			return ShopNameEnum.nmsm;
		}
		if(shopId == ShopIdEnum.mltsm){
			return ShopNameEnum.mltsm;
		}
		return "";
	}
	public static int getShopId(String shopName){
		if(shopName.equals(ShopNameEnum.nmsm)){
			return ShopIdEnum.nmsm;
		}
		if(shopName.equals(ShopNameEnum.mltsm)){
			return ShopIdEnum.mltsm;
		}
		return 0;
	}
	
	
	public static void main(String[] args) {	
		
		InitDb();
		initErpDb();
		
		update();
		
	}
	/*
		String cnOrderCode = "LBX052331406106761";
		int shopId = 5;
		WlbWmsReturnBillGetResponse rsp = getWlbWmsReturnBillGetResponse(cnOrderCode,shopId );
		System.out.println(rsp.getBody());		
		
	 */
	
	public static void update(){
		initErpDb();		
		getNewTrade(ShopIdEnum.nmsm);
		getNewTrade(ShopIdEnum.mltsm);
		
		getRefundTrade(ShopIdEnum.nmsm);
		getRefundTrade(ShopIdEnum.mltsm);
		//updateOldTrade();
	}
	
	public static void InitDb(){
		String dbConfFile = "dbconfig.txt";
		PropKit.use(dbConfFile);
		LOG.info("config load success from " + dbConfFile);
		String jdbcUrl = PropKit.get("jdbcUrl");
		String user = PropKit.get("user");
		String password = PropKit.get("password");
		if (jdbcUrl.length() > 0) {
			C3p0Plugin c3p0Plugin = new C3p0Plugin(jdbcUrl, user, password);
			ActiveRecordPlugin arp = new ActiveRecordPlugin(c3p0Plugin);
			c3p0Plugin.start();
			arp.start();
		}
	}
	
	public static void initErpDb(){
		
		if(DbKit.getConfig("erp")!=null){
			return;
		}
		
		//ERP数据库配置
		String driver="net.sourceforge.jtds.jdbc.Driver";
		String jdbcUrl = "jdbc:jtds:sqlserver://192.168.50.50:1433;databaseName=YMB-NM";
		String user = "sa";
		String password = "ym.12345";	
		Integer maxPoolSize = 1;
		Integer minPoolSize = 1;
		Integer initialPoolSize = 1;
		Integer maxIdleTime =30; 
		Integer acquireIncrement  = 10;
		C3p0Plugin dsErp = new C3p0Plugin(jdbcUrl,user,password,driver,maxPoolSize,minPoolSize,initialPoolSize,maxIdleTime,acquireIncrement);
		ActiveRecordPlugin arpErp = new ActiveRecordPlugin("erp",dsErp);
		arpErp.setDialect(new AnsiSqlDialect()); //此处是重点，不然会出错
				
		dsErp.start();
		arpErp.start();
	}
	
	/*
	 * 单据状态 
	 * WMS_ACCEPT 接单成功 
	 * WMS_REJECT 接单失败 
	 * WMS_CONFIRMED	 * 仓库生产完成，注：此状态表示单据已经在WMS处理完成，可能通过获取单据详情接口获取单据详细信息。 
	 * WMS_CANCEL 取消仓库发货 --
	 * WMS_FAILED 订单发货失败 
	 * TMS_SIGN 买家签收 
	 * TMS_REJECT 买家拒签 
	 * TMS_CANCEL 拦截派送
	 */
	
	/*
	 * 订单类型 201 销售出库 501 退货入库 502 换货出库 503 补发出库904 普通入库 903 普通出库单 306 B2B入库单 305 B2B出库单 601 采购入库 901 退供出库单 701 盘点出库 702 盘点入库 711 库存异动单
	 */

	/*
	 * EMS-504 四川邮政经济快递 1571176573278595 
	 * DISTRIBUTOR_525852 万象-上海-小家电 1911189625755003 
	 * SCKJ-001 成都东骏快捷物流有限公司 1907874082144959 
	 * HMJ_1650965 小家电-新疆黄马甲 1563965710780291
	 * 
	 */
	
	public static void getNewTrade( int shopId) {
		
		try{
			//Date startModifiedTime = DateUtil.getTodayStartTime();
			//Date endModifiedTime = new Date();
			Date startModifiedTime = DateUtil.stringToDate("2016-05-01 00:00:00","yyyy-MM-dd HH:mm:ss");
			Date endModifiedTime = DateUtil.stringToDate("2016-08-18 00:00:00","yyyy-MM-dd HH:mm:ss");
			
			List<CainiaoBillQueryOrderinfolist> cainiaoBillQueryOrderinfolist = getCainiaoBillQueryOrderinfolist("201",startModifiedTime, endModifiedTime, shopId);
			String Shop=getShopName(shopId);
			if(cainiaoBillQueryOrderinfolist==null){
				LOG.info(Shop + "今天没有销售出库单要同步");
				return;
			}
			
			int size = cainiaoBillQueryOrderinfolist.size();

			for (int i = 0; i < size; i++) {
				String cnOrderCode = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getCnOrderCode();
				String order_source_code = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getOrderSourceCodes().get(0);
				
				String webStatus = JdpTbTradeAction.getWebStatus(shopId, order_source_code);
				String tmsStatus = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getStatus();			
				LOG.info("交易编号:" + order_source_code + "--" + "菜鸟状态:" + tmsStatus);
				if (tmsStatus.equals("WMS_CANCEL")) {
					continue;
				}
				if (cnOrderCode != null && !cnOrderCode.equals("")) {
					WlbWmsConsignBillGetResponse rsp = getWlbWmsConsignBillGetResponse(cnOrderCode,shopId);
					//System.out.println(rsp.getBody());
					if (rsp != null && rsp.getErrorCode()==null) {
						String tms_code="";
						String tms_order_code = "";
						String confirm_time = "";
						if(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList()!=null){
							tms_code = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsCode();
							tms_order_code = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsOrderCode();
							confirm_time = DateUtil.dateFmtToString(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getConfirmTime());
						}
					
						String StoreCode = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getStoreCode();
						String StoreName = "";
						String ItemCode = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getOrderItemList().get(0).getOrderItem().getItemCode();
						int ItemQty = Integer.parseInt(String.valueOf(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getOrderItemList().get(0).getOrderItem().getInventoryItemList().get(0).getInventoryItem().getItemQty()));
						
						saveTradeCainiao(order_source_code,cnOrderCode,Shop,webStatus,tmsStatus,tms_code,"",tms_order_code,confirm_time,StoreCode,StoreName,ItemCode,ItemQty);
						
						LOG.info("交易编号:" + order_source_code+ "--" + "订单状态:" + webStatus + "--" + "菜鸟状态:" + tmsStatus + "--" + "快递方式:" + tms_code + "--" + "快递单号:" + tms_order_code + "--" + "完成时间:" + confirm_time);
					}
				}
				else{
					LOG.error("API错误:");
				}
			}
			
			
		}catch(Exception e){
			LOG.error("未知错误:" + e.getMessage() );
		}

	}
	
	public static void updateOldTrade(){
		String sql = "SELECT * FROM nw_Trade_cainiao WHERE WebStatus NOT IN ('TRADE_FINISHED','TRADE_CLOSED') OR TmsStatus NOT IN ('TMS_SIGN') AND CnOrderCode IS NOT NULL";
		List<Record> tradeList =  Db.use("erp").find(sql);
		if(tradeList.size()>=1){
			LOG.info("共有" + tradeList.size() + "个菜鸟出库单信息需要更新");
			for(int i =0;i<tradeList.size();i++){
				String cnOrderCode = tradeList.get(i).getStr("CnOrderCode");
				String order_source_code = tradeList.get(i).getStr("TradeNo");
				String Shop = tradeList.get(i).getStr("Shop");
				int shopId = getShopId(Shop);
				
				String webStatus = JdpTbTradeAction.getWebStatus(shopId, order_source_code);
				String tmsStatus = "";
				String tms_code="";
				String tms_order_code = "";
				String confirm_time = "";
				if (cnOrderCode != null && !cnOrderCode.equals("")) {
					WlbWmsConsignBillGetResponse rsp = getWlbWmsConsignBillGetResponse(cnOrderCode,shopId);
					if (rsp != null && rsp.getErrorCode()==null) {
						tmsStatus = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getStatus();
						if(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList()!=null){
							tms_code = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsCode();
							tms_order_code = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsOrderCode();
							confirm_time = DateUtil.dateFmtToString(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getConfirmTime());
						}
						String StoreCode = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getStoreCode();
						String StoreName = "";
						String ItemCode = rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getOrderItemList().get(0).getOrderItem().getItemCode();
						int ItemQty = Integer.parseInt(String.valueOf(rsp.getConsignSendInfoList().get(0).getConsignSendInfo().getOrderItemList().get(0).getOrderItem().getInventoryItemList().get(0).getInventoryItem().getItemQty()));
						
						saveTradeCainiao(order_source_code,cnOrderCode,Shop,webStatus,tmsStatus,tms_code,"",tms_order_code,confirm_time,StoreCode,StoreName,ItemCode,ItemQty);
						
						LOG.info("交易编号:" + order_source_code+ "--" + "订单状态:" + webStatus + "--" + "菜鸟状态:" + tmsStatus + "--" + "快递方式:" + tms_code + "--" + "快递单号:" + tms_order_code + "--" + "完成时间:" + confirm_time);
					}
				}
			}
		}
		
	}
	
	public static void getRefundTrade(int shopId){
		//Date startModifiedTime = DateUtil.getTodayStartTime();
		//Date endModifiedTime = new Date();
		Date startModifiedTime = DateUtil.stringToDate("2016-05-01 00:00:00","yyyy-MM-dd HH:mm:ss");
		Date endModifiedTime = DateUtil.stringToDate("2016-08-19 00:00:00","yyyy-MM-dd HH:mm:ss");
		
		List<CainiaoBillQueryOrderinfolist> cainiaoBillQueryOrderinfolist = getCainiaoBillQueryOrderinfolist("501",startModifiedTime, endModifiedTime, shopId);
		String Shop=getShopName(shopId);
		if(cainiaoBillQueryOrderinfolist==null){
			LOG.info(Shop + "今天没有退货入库单要同步");
			return;
		}
		if(cainiaoBillQueryOrderinfolist!=null){
			for(int i=0;i<cainiaoBillQueryOrderinfolist.size();i++){
				//退货单编号
				String cnOrderCode = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getCnOrderCode();
				String order_source_code = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getOrderSourceCodes().get(0);
				String webStatus = JdpTbTradeAction.getWebStatus(shopId, order_source_code);
				String tmsStatus = cainiaoBillQueryOrderinfolist.get(i).getOrderInfo().getStatus();	
				LOG.info("交易编号:" + order_source_code + "--" + "菜鸟状态:" + tmsStatus);
				if (cnOrderCode != null && !cnOrderCode.equals("")) {
					
					WlbWmsReturnBillGetResponse rsp = getWlbWmsReturnBillGetResponse(cnOrderCode,shopId);
					System.out.println(rsp.getBody());
					if (rsp != null && rsp.getErrorCode()==null) {
						String confirm_time = "";
						if(rsp.getReturnOrderInfo().getConfirmTime()!=null){
							//退货完成时间
							confirm_time = DateUtil.dateFmtToString(rsp.getReturnOrderInfo().getConfirmTime());
						}
						String ItemCode = rsp.getReturnOrderInfo().getOrderItemList().get(0).getOrderItem().getItemCode();
						int ItemQty = Integer.parseInt(String.valueOf(rsp.getReturnOrderInfo().getOrderItemList().get(0).getOrderItem().getInventoryItemList().get(0).getInventoryItem().getItemQty()));
						
						//发货单编号
						String preCnOrderCode = rsp.getReturnOrderInfo().getPreCnOrderCode();
						WlbWmsConsignBillGetResponse prsp = getWlbWmsConsignBillGetResponse(preCnOrderCode,shopId);
						//System.out.println(rsp.getBody());
						String tms_code="";
						String tms_order_code = "";
						String StoreCode = "";
						String StoreName = "";
						if (prsp != null && prsp.getErrorCode()==null) {
							if(prsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList()!=null){
								tms_code = prsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsCode();
								tms_order_code = prsp.getConsignSendInfoList().get(0).getConsignSendInfo().getTmsOrderList().get(0).getTmsOrder().getTmsOrderCode();
							}
							StoreCode = prsp.getConsignSendInfoList().get(0).getConsignSendInfo().getStoreCode();
						}
						
						saveTradeCainiaoRefund(order_source_code,cnOrderCode,Shop,webStatus,tmsStatus,tms_code,"",tms_order_code,confirm_time,StoreCode,StoreName,ItemCode,ItemQty,preCnOrderCode);
						LOG.info("交易编号:" + order_source_code+ "--" + "订单状态:" + webStatus + "--" + "菜鸟状态:" + tmsStatus + "--" + "快递方式:" + tms_code + "--" + "快递单号:" + tms_order_code + "--" + "完成时间:" + confirm_time);
					}
				}
				else{
					LOG.error("API错误:");
				}
				
			}
			
		}
	}

	public static List<CainiaoBillQueryOrderinfolist> getCainiaoBillQueryOrderinfolist(String orderType,Date startModifiedTime, Date endModifiedTime, int shopId) {

		List<CainiaoBillQueryOrderinfolist> cainiaoBillQueryOrderinfolist = null;
		WlbWmsCainiaoBillQueryResponse rsp = getWlbWmsCainiaoBillQueryResponse(orderType,startModifiedTime, endModifiedTime, 1, 50, shopId);
		LOG.info(rsp.getBody());
		if (rsp != null && rsp.getTotalCount() !=null) {
			int total_count = Integer.parseInt(String.valueOf(rsp.getTotalCount()));
			if (total_count > 50) {
				int maxPage = total_count / 50 + 1;
				cainiaoBillQueryOrderinfolist = rsp.getOrderInfoList();
				for (int i = 2; i <= maxPage; i++) {
					WlbWmsCainiaoBillQueryResponse nrsp = getWlbWmsCainiaoBillQueryResponse(orderType,startModifiedTime, endModifiedTime, i, 50, shopId);
					List<CainiaoBillQueryOrderinfolist> cbqr = nrsp.getOrderInfoList();
					for (int j = 0; j < cbqr.size(); j++) {
						CainiaoBillQueryOrderinfolist cboi = cbqr.get(j);
						cainiaoBillQueryOrderinfolist.add(cboi);
					}
				}
			} else {
				cainiaoBillQueryOrderinfolist = rsp.getOrderInfoList();
				return cainiaoBillQueryOrderinfolist;
			}
		} else {
			LOG.error("API调用失败");
		}
		return cainiaoBillQueryOrderinfolist;
	}

	public static WlbWmsCainiaoBillQueryResponse getWlbWmsCainiaoBillQueryResponse(String orderType,Date startModifiedTime, Date endModifiedTime, int pageNo, int pageSize, int shopId) {
		String getShopSql = "select SHOP_ID,SHOP_NAME,SHOP_API_APP_KEY,SHOP_API_ACCESS_TOKEN,SHOP_API_APP_SECRECT from shop where SHOP_ID='" + shopId + "'";
		Record getShopRec = Db.findFirst(getShopSql);
		@SuppressWarnings("unused")
		String seller_nick = "";
		String sessionKey = "";
		String appkey = "";
		String secret = "";
		if (getShopRec != null) {
			seller_nick = getShopRec.getStr("SHOP_NAME");
			sessionKey = getShopRec.getStr("SHOP_API_ACCESS_TOKEN");
			appkey = getShopRec.getStr("SHOP_API_APP_KEY");
			secret = getShopRec.getStr("SHOP_API_APP_SECRECT");
		} else {
			return null;
		}
		TaobaoClient client = new DefaultTaobaoClient(url, appkey, secret);
		WlbWmsCainiaoBillQueryRequest req = new WlbWmsCainiaoBillQueryRequest();
		req.setStartModifiedTime(startModifiedTime);
		req.setEndModifiedTime(endModifiedTime);
		req.setOrderType(orderType);
		req.setPageNo(Long.parseLong(String.valueOf(pageNo)));
		req.setPageSize(Long.parseLong(String.valueOf(pageSize)));
		WlbWmsCainiaoBillQueryResponse rsp = null;
		try {
			rsp = client.execute(req, sessionKey);
			if(rsp.getMsg() !=null && rsp.getMsg().contains("Invalid")){
				rsp = client.execute(req, sessionKey);
			}
		} catch (ApiException e) {
			try {
				rsp = client.execute(req, sessionKey);
			} catch (ApiException e1) {
				try {
					rsp = client.execute(req, sessionKey);
				} catch (ApiException e2) {
					e2.printStackTrace();
				}
			}
		}
		catch(Exception e){
			LOG.error(e.getMessage());
		}
		// System.out.println(rsp.getBody());
		return rsp;
	}
	
	/**
	 * 获取销售订单发货信息
	 * @param cnOrderCode
	 * @param shopId
	 * @return
	 */
	public static WlbWmsConsignBillGetResponse getWlbWmsConsignBillGetResponse(String cnOrderCode, int shopId) {
		String getShopSql = "select SHOP_ID,SHOP_NAME,SHOP_API_APP_KEY,SHOP_API_ACCESS_TOKEN,SHOP_API_APP_SECRECT from shop where SHOP_ID='" + shopId + "'";
		Record getShopRec = Db.findFirst(getShopSql);
		@SuppressWarnings("unused")
		String seller_nick = "";
		String sessionKey = "";
		String appkey = "";
		String secret = "";
		if (getShopRec != null) {
			seller_nick = getShopRec.getStr("SHOP_NAME");
			sessionKey = getShopRec.getStr("SHOP_API_ACCESS_TOKEN");
			appkey = getShopRec.getStr("SHOP_API_APP_KEY");
			secret = getShopRec.getStr("SHOP_API_APP_SECRECT");
		} else {
			return null;
		}
		TaobaoClient client = new DefaultTaobaoClient(url, appkey, secret);
		WlbWmsConsignBillGetRequest req = new WlbWmsConsignBillGetRequest();
		req.setCnOrderCode(cnOrderCode);
		WlbWmsConsignBillGetResponse rsp = null;
		try {
			rsp = client.execute(req, sessionKey);
		} catch (ApiException e) {
			try {
				rsp = client.execute(req, sessionKey);
			} catch (ApiException e1) {
				try {
					rsp = client.execute(req, sessionKey);
				} catch (ApiException e2) {
					e2.printStackTrace();
				}
			}
		}
		// System.out.println(rsp.getBody());
		return rsp;
	}
	
	/**
	 * 获取销退收货信息
	 * @param cnOrderCode
	 * @param shopId
	 * @return
	 */
	public static WlbWmsReturnBillGetResponse getWlbWmsReturnBillGetResponse(String cnOrderCode, int shopId) {
		String getShopSql = "select SHOP_ID,SHOP_NAME,SHOP_API_APP_KEY,SHOP_API_ACCESS_TOKEN,SHOP_API_APP_SECRECT from shop where SHOP_ID='" + shopId + "'";
		Record getShopRec = Db.findFirst(getShopSql);
		@SuppressWarnings("unused")
		String seller_nick = "";
		String sessionKey = "";
		String appkey = "";
		String secret = "";
		if (getShopRec != null) {
			seller_nick = getShopRec.getStr("SHOP_NAME");
			sessionKey = getShopRec.getStr("SHOP_API_ACCESS_TOKEN");
			appkey = getShopRec.getStr("SHOP_API_APP_KEY");
			secret = getShopRec.getStr("SHOP_API_APP_SECRECT");
		} else {
			return null;
		}
		TaobaoClient client = new DefaultTaobaoClient(url, appkey, secret);
		WlbWmsReturnBillGetRequest req = new WlbWmsReturnBillGetRequest();
		req.setCnOrderCode(cnOrderCode);
		WlbWmsReturnBillGetResponse rsp = null;
		try {
			rsp = client.execute(req, sessionKey);
		} catch (ApiException e) {
			try {
				rsp = client.execute(req, sessionKey);
			} catch (ApiException e1) {
				try {
					rsp = client.execute(req, sessionKey);
				} catch (ApiException e2) {
					e2.printStackTrace();
				}
			}
		}
		// System.out.println(rsp.getBody());
		return rsp;
	}
	
	public static void saveTradeCainiao(String TradeNo,String CnOrderCode,String Shop,String WebStatus,String TmsStatus,String SendCode,String SendStyle,String PostBarCode
			,String SndTime,String StoreCode,String StoreName,String ItemCode,int ItemQty){
		String checkSql  = "SELECT COUNT(*) FROM nw_Trade_cainiao WHERE TradeNo = '" + TradeNo + "'";
		String insertSql="INSERT INTO nw_Trade_cainiao(TradeNo,CnOrderCode,Shop,WebStatus,TmsStatus,SendCode,SendStyle,PostBarCode,SndTime,StoreCode,StoreName,ItemCode,ItemQty,FLAG) VALUES("
				+ " '" + TradeNo + "'"
				+ ",'" + CnOrderCode + "'"
				+ ",'" + Shop + "'"
				+ ",'" + WebStatus + "'"
				+ ",'" + TmsStatus + "'"
				+ ",'" + SendCode + "'"
				+ ",'" + SendStyle + "'"
				+ ",'" + PostBarCode + "'"
				+ ",'" + SndTime + "'"
				+ ",'" + StoreCode + "'"
				+ ",'" + StoreName + "'"
				+ ",'" + ItemCode + "'"
				+ ",'" + ItemQty + "'"
				+ ",0"
				+ ")";		
		String updateSql = "UPDATE nw_Trade_cainiao SET "
				+ " CnOrderCode='" + CnOrderCode + "'"
				+ ",WebStatus='" + WebStatus + "'"
				+ ",TmsStatus='" + TmsStatus + "'"
				+ ",SendCode='" + SendCode + "'"
				+ ",SendStyle='" + SendStyle + "'"
				+ ",PostBarCode='" + PostBarCode + "'"
				+ ",SndTime=CAST(nullif('" + SndTime + "','') as datetime)"
				+ ",StoreCode='" + StoreCode + "'"
				+ ",StoreName='" + StoreName + "'"
				+ ",ItemCode='" + ItemCode + "'"
				+ ",ItemQty='" + ItemQty + "'"
				+ ",Modify_DateTime=getdate()"
				+ ",FLAG=2"
				+ " WHERE TradeNo='" + TradeNo +"'";
		
		int c= Db.use("erp").queryInt(checkSql);
		if(c>=1){
			Db.use("erp").update(updateSql);
		}else{
			Db.use("erp").update(insertSql);
		}
	}
	
	
	public static void saveTradeCainiaoRefund(String TradeNo,String CnOrderCode,String Shop,String WebStatus,String TmsStatus,String SendCode,String SendStyle,String PostBarCode
			,String SndTime,String StoreCode,String StoreName,String ItemCode,int ItemQty,String preCnOrderCode){
		String checkSql  = "SELECT COUNT(*) FROM nw_Trade_cainiao_refund WHERE TradeNo = '" + TradeNo + "'";
		String insertSql="INSERT INTO nw_Trade_cainiao_refund(TradeNo,CnOrderCode,Shop,WebStatus,TmsStatus,SendCode,SendStyle,PostBarCode,SndTime,StoreCode,StoreName,ItemCode,ItemQty,PreCnOrderCode,FLAG) VALUES("
				+ " '" + TradeNo + "'"
				+ ",'" + CnOrderCode + "'"
				+ ",'" + Shop + "'"
				+ ",'" + WebStatus + "'"
				+ ",'" + TmsStatus + "'"
				+ ",'" + SendCode + "'"
				+ ",'" + SendStyle + "'"
				+ ",'" + PostBarCode + "'"
				+ ",'" + SndTime + "'"
				+ ",'" + StoreCode + "'"
				+ ",'" + StoreName + "'"
				+ ",'" + ItemCode + "'"
				+ ",'" + ItemQty + "'"
				+ ",'" + preCnOrderCode + "'"
				+ ",0"
				+ ")";		
		String updateSql = "UPDATE nw_Trade_cainiao_refund SET "
				+ " CnOrderCode='" + CnOrderCode + "'"
				+ ",WebStatus='" + WebStatus + "'"
				+ ",TmsStatus='" + TmsStatus + "'"
				+ ",SendCode='" + SendCode + "'"
				+ ",SendStyle='" + SendStyle + "'"
				+ ",PostBarCode='" + PostBarCode + "'"
				+ ",SndTime=CAST(nullif('" + SndTime + "','') as datetime)"
				+ ",StoreCode='" + StoreCode + "'"
				+ ",StoreName='" + StoreName + "'"
				+ ",ItemCode='" + ItemCode + "'"
				+ ",ItemQty='" + ItemQty + "'"
				+ ",PreCnOrderCode='" + preCnOrderCode + "'"
				+ ",Modify_DateTime=getdate()"
				+ ",FLAG=2"
				+ " WHERE TradeNo='" + TradeNo +"'";
		
		int c= Db.use("erp").queryInt(checkSql);
		if(c>=1){
			Db.use("erp").update(updateSql);
		}else{
			Db.use("erp").update(insertSql);
		}
	}
	
	/*-----------------------------------------------------------------------------------------*/
}
